#include "MysqlDao.hpp"
#include "const.hpp"
#include <chrono>
#include <jdbc/cppconn/driver.h>
#include <jdbc/cppconn/exception.h>
#include <jdbc/cppconn/prepared_statement.h>
#include <jdbc/cppconn/resultset.h>
#include <jdbc/cppconn/statement.h>
#include <jdbc/mysql_connection.h>
#include <jdbc/mysql_driver.h>
#include <memory>
#include <mutex>
#include <ostream>
#include <string>
#include <thread>

SqlConnect::SqlConnect(sql::Connection *con, int64_t lastTime)
    : _con(con), _last_oper_time(lastTime) {}

MysqlPool::MysqlPool(const std::string &url, const std::string &user,
                     const std::string &pass, const std::string &schema,
                     int poolSize)
    : _url(url), _user(user), _passwd(pass), _schema(schema),
      _poolSize(poolSize), _b_stop(false) {
  try {
    for (int i = 0; i < _poolSize; ++i) {
      sql::mysql::MySQL_Driver *driver =
          sql::mysql::get_mysql_driver_instance();
      auto *con = driver->connect(_url, _user, _passwd);
      con->setSchema(_schema);
      // 获取当前时间戳
      auto currentTime = std::chrono::system_clock::now().time_since_epoch();
      // 将时间戳转换为秒
      long long timeStamp =
          std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
      _pool.push(std::make_unique<SqlConnect>(con, timeStamp));
    }

    _check_thread = std::thread([this] {
      while (!_b_stop) {
        checkConnection();
        std::this_thread::sleep_for(std::chrono::seconds(60));
      }
    });
    _check_thread.detach();

  } catch (sql::SQLException &e) {

    std::cout << "mysql pool init failed, error is " << e.getSQLStateCStr()
              << std::endl;
    ;
  }
}

void MysqlPool::checkConnection() {
  std::lock_guard<std::mutex> lock(_mutex);
  int poolSize = _pool.size();
  auto currentTime = std::chrono::system_clock::now().time_since_epoch();
  long long timeTamp =
      std::chrono::duration_cast<std::chrono::seconds>(currentTime).count();
  for (int i = 0; i < poolSize; ++i) {
    auto con = std::move(_pool.front());
    _pool.pop();
    if (timeTamp - con->_last_oper_time < 5) {
      continue;
    }
    try {
      std::unique_ptr<sql::Statement> stmt(con->_con->createStatement());
      stmt->executeQuery("SELECT 1;");
      con->_last_oper_time = timeTamp;
      // std::cout << "execute timer alive query, cur is " << timeTamp
      //           << std::endl;
    } catch (sql::SQLException &e) {
      std::cout << "Error keeping connection alive: " << e.what() << std::endl;
      // 重新创建连接并替换旧连接
      sql::mysql::MySQL_Driver *driver =
          sql::mysql::get_mysql_driver_instance();
      auto *new_con = driver->connect(_url, _user, _passwd);
      new_con->setSchema(_schema);
      con->_con.reset(new_con);
      con->_last_oper_time = timeTamp;
    }
    _pool.push(std::move(con));
  }
}
std::unique_ptr<SqlConnect> MysqlPool::getConnection() {
  std::unique_lock<std::mutex> lock(_mutex);
  _cond.wait(lock, [this]() { return _b_stop || !_pool.empty(); });
  if (_b_stop) {
    return nullptr;
  }
  auto con = std::move(_pool.front());
  _pool.pop();
  return con;
}

void MysqlPool::returnConnect(std::unique_ptr<SqlConnect> con) {
  std::unique_lock<std::mutex> lock(_mutex);
  if (_b_stop) {
    return;
  }
  _pool.push(std::move(con));
  _cond.notify_one();
}

void MysqlPool::Close() {
  _b_stop = true;
  _cond.notify_all();
}

MysqlPool::~MysqlPool() {
  std::lock_guard<std::mutex> lock(_mutex);
  while (!_pool.empty()) {
    _pool.pop();
  }
  Close();
}

MysqlDao::MysqlDao() {
  const auto &host = config.get<std::string>("Mysql.Host");
  const auto &port = config.get<std::string>("Mysql.Port");
  const auto &user = config.get<std::string>("Mysql.User");
  const auto &password = config.get<std::string>("Mysql.PassWord");
  const auto &scheme = config.get<std::string>("Mysql.Scheme");
  const auto &poolSize = config.get<int>("Mysql.PoolSize");
  _pool.reset(
      new MysqlPool(host + ":" + port, user, password, scheme, poolSize));
}

MysqlDao::~MysqlDao() { _pool->Close(); }

// 用户注册逻辑
int MysqlDao::RegUser(const std::string &name, const std::string &email,
                      const std::string &pwd) {
  if (name.empty() || email.empty() || pwd.empty()) {
    std::cout << "name or email or pwd is empty." << std::endl;
    return -1;
  }
  auto con = _pool->getConnection();
  try {
    if (con == nullptr) {
      return -1;
    }
    con->_con->setAutoCommit(true);
    // 查询用户名或者邮箱是否存在
    std::string sql = "SELECT * FROM user WHERE name= ? OR email= ?;";
    std::unique_ptr<sql::PreparedStatement> statement(
        con->_con->prepareStatement(sql));
    statement->setString(1, name);
    statement->setString(2, email);
    std::unique_ptr<sql::ResultSet> res(statement->executeQuery());
    // 查询到相关记录
    while (res->next()) {
      // 用户名存在
      if (res->getString("name") == name) {
        std::cout << "name exist." << std::endl;
        _pool->returnConnect(std::move(con));
        return -1;
      }
      // 邮箱存在
      if (res->getString("email") == email) {
        std::cout << "email exist." << std::endl;
        _pool->returnConnect(std::move(con));
        return -1;
      }
    }
    // 用户名和邮箱均不存在, 注册用户
    // 更新 user_id 表的 id字段
    sql = "SELECT id FROM user_id;";
    statement.reset(con->_con->prepareStatement(sql));
    res.reset(statement->executeQuery());
    if (!res->next()) {
      _pool->returnConnect(std::move(con));
      std::cout << "query user_id table failed." << std::endl;
      return -1;
    }
    con->_con->setAutoCommit(false);
    int uid = res->getInt64("id");
    sql = "UPDATE user_id SET id = ?;";
    statement.reset(con->_con->prepareStatement(sql));
    statement->setInt64(1, uid + 1);
    if (statement->executeUpdate() == 0) {
      con->_con->rollback();
      _pool->returnConnect(std::move(con));
      std::cout << "user_id table update failed." << std::endl;
      return -1;
    }
    sql = "INSERT INTO user VALUES (NULL, ?, ?, ?, ?);";
    statement.reset(con->_con->prepareStatement(sql));
    statement->setInt64(1, uid + 1);
    statement->setString(2, name);
    statement->setString(3, email);
    statement->setString(4, pwd);
    if (statement->executeUpdate() == 0) {
      con->_con->rollback();
      _pool->returnConnect(std::move(con));
      std::cout << "insert failed." << std::endl;
      return -1;
    } else {
      con->_con->commit();
      _pool->returnConnect(std::move(con));
      return uid + 1;
    }
  } catch (sql::SQLException &e) {
    if (!con->_con->getAutoCommit()) {
      con->_con->rollback();
    }
    _pool->returnConnect(std::move(con));
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return -1;
  }
}

// 检查邮箱
bool MysqlDao::CheckEmail(const std::string &email) {
  // 获取mysql连接
  std::unique_ptr<SqlConnect> con = _pool->getConnection();
  try {
    if (con == nullptr) {
      return false;
    }
    con->_con->setAutoCommit(true);
    // 查询用户名和邮箱是否存在
    std::string sql = "SELECT * FROM user WHERE email=?;";
    auto state = con->_con->prepareStatement(sql);
    state->setString(1, email);
    auto res = state->executeQuery();
    if (!res->next()) {
      // 邮箱和用户名不匹配
      std::cout << "Email does not match." << std::endl;
      _pool->returnConnect(std::move(con));
      return false;
    }
    _pool->returnConnect(std::move(con));
    return true;
  } catch (sql::SQLException &e) {
    _pool->returnConnect(std::move(con));
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

// 更新密码
bool MysqlDao::UpdatePwd(const std::string &email, const std::string &newPwd) {
  // 获取mysql连接
  std::unique_ptr<SqlConnect> con = _pool->getConnection();
  try {
    if (con == nullptr) {
      return false;
    }
    con->_con->setAutoCommit(true);
    std::string sql = "UPDATE user SET pwd=? WHERE email=?;";
    sql::PreparedStatement *state = con->_con->prepareStatement(sql);
    state->setString(1, newPwd);
    state->setString(2, email);
    if (state->executeUpdate() == 0) {
      _pool->returnConnect(std::move(con));
      std::cout << "update password failed." << std::endl;
      return false;
    }
    _pool->returnConnect(std::move(con));
    return true;
  } catch (sql::SQLException &e) {
    _pool->returnConnect(std::move(con));
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}

bool MysqlDao::CheckPwd(const std::string &email, const std::string &pwd,
                        UserInfo &userInfo) {
  auto con = _pool->getConnection();
  if (con == nullptr) {
    return false;
  }
  try {
    std::string sql = "SELECT * FROM user WHERE email=? AND pwd=?;";
    auto state = con->_con->prepareStatement(sql);
    state->setString(1, email);
    state->setString(2, pwd);
    sql::ResultSet *res = state->executeQuery();
    if (!res->next()) {
      _pool->returnConnect(std::move(con));
      return false;
    }
    userInfo.uid = res->getUInt("uid");
    userInfo.email = res->getString("email");
    userInfo.name = res->getString("name");
    userInfo.pwd = pwd;
    _pool->returnConnect(std::move(con));
    return true;
  } catch (sql::SQLException &e) {
    _pool->returnConnect(std::move(con));
    std::cerr << "SQLException: " << e.what();
    std::cerr << " (MySQL error code: " << e.getErrorCode();
    std::cerr << ",SQLState: " << e.getSQLState() << " )" << std::endl;
    return false;
  }
}